﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using MiscLib;
using System.Web;
using System.IO;
using FCDAL;
using FCModel;

namespace FCBLL
{
    public class HookUPQPMBLL
    {

        protected rt_QPM2_Hookup QPM2HookUP = new rt_QPM2_Hookup();

        public static DataTable IntiData(bool flag)
        {
            //HDDFis.RtTrace _Trace = new HDDFis.RtTrace();
            //string conn_str = _Trace.ConnectionString;
            //string sql = GetSql(flag);

            //string str_dt = GetDateTimeStamp();
            //DataTable dt_0 = QPMDAL.GetData(sql, conn_str);
            //return dt_0;
            DataTable dt = new DataTable();
            return dt;
        }

        public static DataTable GetTransposeDT(DataTable dt, bool flag)
        {
            DataTable rt_dt = new DataTable("Table");
            rt_dt.Columns.Add("Parameter", Type.GetType("System.String"));
            rt_dt.Columns.Add("Value", Type.GetType("System.Object"));

            if (flag)
            {
                for (int i = 0; i < 20; i++)
                {
                    DataRow dr = rt_dt.NewRow();
                    dr[0] = dt.Columns[i].ColumnName;
                    rt_dt.Rows.Add(dr);
                }
                rt_dt.Rows.RemoveAt(11);   //去掉 [HOOKUP_2D_BARCODE]
                rt_dt.Rows.RemoveAt(11);   //去掉 [ACA_DATE_CODE]
            }
            else
            {
                for (int i = 0; i < 5; i++)
                {
                    DataRow dr = rt_dt.NewRow();
                    dr[0] = dt.Columns[i].ColumnName;
                    rt_dt.Rows.Add(dr);
                }
            }
            return rt_dt;
        }

        private static string GetDateTimeStamp()
        {
            return DateTime.Now.ToString("yyyyMMddHHmmssfff");
        }

        public static string GetSql(bool flag)
        {
            string sql = "";
            if (flag)
            {
                sql = "select * from rt_QPM_Hookup_New(nolock) where null=null";
            }
            else
            {
                sql = "select * from rt_QPMSpec_Hookup(nolock) where null=null";
            }
            return sql;
        }

        private static string IsQutotedStr(string colname, object obj_val, DataTable dt_0)
        {
            if (dt_0.Columns[colname].DataType == Type.GetType("System.DateTime"))
                return "\"" + ((DateTime)obj_val).ToString("yyyyMMddHHmmss").Trim() + "\"";
            for (int i = 31; i <= 35; i++)
            {
                string col = "SPARE_";
                col = col + i.ToString();
                if (colname == col)
                {
                    return obj_val.ToString().Trim().ToUpper();
                }
            }

            if (dt_0.Columns[colname].DataType != Type.GetType("System.Single") && dt_0.Columns[colname].DataType != Type.GetType("System.Int32"))
                return "\"" + obj_val.ToString().Trim().ToUpper() + "\"";
            return obj_val.ToString().Trim().ToUpper();
        }

        //public static void HookUPQDMToStr(string filename, string static_info, string txt, DataTable dt_val, DataTable dt_0, HttpResponse Response, HttpServerUtility Server)
        //{
        //    StringBuilder sb=new StringBuilder();
        //    sb.Append(static_info);
        //    sb.Append("# DFD 1.14" + Environment.NewLine);
        //    string file_head = string.Empty;
        //    string str = "";
        //    for (int j = 0; j < dt_val.Columns.Count; j++)
        //    {
        //        file_head += "\"" + dt_val.Columns[j].ColumnName.Trim().ToUpper() + "\"" + ",";
        //    }
        //    file_head = file_head.Substring(0, file_head.Length - 1) + Environment.NewLine;
        //    sb.Append(file_head);
        //    for (int i = 0; i < dt_val.Rows.Count; i++)
        //    {
        //        for (int j = 0; j < dt_val.Columns.Count; j++)
        //        {
        //            str += IsQutotedStr(dt_val.Columns[j].ColumnName, dt_val.Rows[i][j], dt_0) + ",";
        //        }
        //        str = str.Substring(0, str.Length - 1) + Environment.NewLine;
        //        sb.Append(str);
        //        str = "";
        //    }
        //    sb.Append("EOF");
        //    //static_info = static_info.Replace("HOOKUP", "PCBA");
        //    ExcelExport.OutputTxt(filename, sb.ToString(), Response, Server);
        //    string save_str = static_info + "\"FILE_NAME\",\"FILE_CREATION_DATE\",\"NUMBER_OF_RECORDS\"" + Environment.NewLine + "\"" + new FileInfo(filename).Name + "\",\"" + txt + "\"," + dt_val.Rows.Count + Environment.NewLine + "EOF";
        //    filename = filename.Replace("PCBADFD", "PCBAMF");
        //    ExcelExport.OutputTxt(filename, save_str, Response, Server);
        //}

        public static string HookUPQPMToStr(DataTable dt_Excel, List<string> _list_val, string static_info, string file_head, string DFDRev, Dictionary<string, string> dc_txt)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(static_info);
            sb.Append("# DFD " + DFDRev + Environment.NewLine);

            for (int j = 0; j < _list_val.Count; j++)
            {
                file_head += "\"" + _list_val[j].Trim().ToUpper() + "\"" + ",";
            }
            file_head = file_head.Substring(0, file_head.Length - 1) + Environment.NewLine;
            sb.Append(file_head);
            for (int i = 0; i < dt_Excel.Rows.Count; i++)
            {
                string str = "";
                str += "\"" + dc_txt["MFG_SITE_DUNS"] + "\"" + ",";
                str += "\"" + dc_txt["SUB_SUPPLIER_NAME"] + "\"" + ",";
                str += "\"" + dc_txt["DATA_SOURCE_TYPE"] + "\"" + ",";
                str += "\"" + dc_txt["PART_NUM"] + "\"" + ",";
                str += "\"" + dc_txt["PART_REV"] + "\"" + ",";
                str += "\"" + dc_txt["DRAWING_NUM"] + "\"" + ",";
                str += "\"" + dc_txt["DRAWING_REV"] + "\"" + ",";
                str += "\"" + dc_txt["GROUP_DATETIME"] + "\"" + ",";
                str += "\"" + dc_txt["SBR_NUM"] + "\"" + ",";
                str += "\"" + dc_txt["LOT_NUM"] + "\"" + ",";
                str += "\"" + dc_txt["RECORD_DATETIME"] + "\"" + ",";
                str += (i + 1).ToString() + ",";
                str += "\"" + dc_txt["REFERENCE_PART_NUM"] + "\"" + ",";
                str += "\"" + dc_txt["REFERENCE_PART_REV"] + "\"" + ",";
                for (int j = 14; j < _list_val.Count; j++)
                {
                    if ((_list_val[j].ToString() == "DHS_INSP" || _list_val[j].ToString() == "DHS_SURFACE_AREA" || _list_val[j].ToString() == "FTIR_INSP" || _list_val[j].ToString() == "FTIR_SURFACE_AREA" || _list_val[j].ToString() == "PARTICLE_LPC_INSP" || _list_val[j].ToString() == "PARTICLE_LPC_SURFACE_AREA" || _list_val[j].ToString() == "PARTICLE_HPA_INSP" || _list_val[j].ToString() == "PARTICLE_HPA_SURFACE_AREA" || _list_val[j].ToString() == "IONIC_INSP" || _list_val[j].ToString() == "IONIC_SURFACE_AREA"))
                    //&& dgv_excel.Rows[i].Cells[j].Value.ToString() != "")
                    {
                        str += "\"" + dt_Excel.Rows[i][j].ToString() + "\",";
                    }
                    else
                    {
                        str += dt_Excel.Rows[i][j].ToString() + ",";
                    }
                }
                str = str.Substring(0, str.Length - 1) + Environment.NewLine;
                sb.Append(str);
            }
            sb.Append("EOF");
            return sb.ToString();
        }

        //public static void HookUPQPMSpecToStr(string filename, string static_info, string txt, DataTable dt_val, DataTable dt_0, HttpResponse Response, HttpServerUtility Server)
        //{
        //    StringBuilder sb = new StringBuilder();
        //    string str = "";
        //    sb.Append(static_info);
        //    sb.Append("# DFD 1.3" + Environment.NewLine);
        //    string file_head = string.Empty;
        //    for (int j = 0; j < dt_val.Columns.Count; j++)
        //    {
        //        file_head += "\"" + dt_val.Columns[j].ColumnName.Trim().ToUpper() + "\"" + ",";
        //    }
        //    file_head = file_head.Substring(0, file_head.Length - 1) + Environment.NewLine;
        //    sb.Append(file_head);
        //    for (int i = 0; i < dt_val.Rows.Count; i++)
        //    {
        //        for (int j = 0; j < dt_val.Columns.Count; j++)
        //        {
        //            str += IsQutotedStr(dt_val.Columns[j].ColumnName, dt_val.Rows[i][j], dt_0) + ",";
        //        }
        //        str = str.Substring(0, str.Length - 1) + Environment.NewLine;
        //        sb.Append(str);
        //        str = "";
        //    }
        //    sb.Append("EOF");
        //    ExcelExport.OutputTxt(filename, sb.ToString(), Response, Server);
        //    //static_info = static_info.Replace("HOOKUP", "PCBA");
        //    string save_str = static_info + "\"FILE_NAME\",\"FILE_CREATION_DATE\",\"NUMBER_OF_RECORDS\"" + Environment.NewLine + "\"" + new FileInfo(filename).Name + "\",\"" + txt + "\"," + dt_val.Rows.Count + Environment.NewLine + "EOF";
        //    filename = filename.Replace("PCBADFD", "PCBAMF");
        //    ExcelExport.OutputTxt(filename, save_str, Response, Server);
        //}

        private void InsertData(DataTable dt_1, DataTable dt_2, DataTable dt_0, DateTime dt_import)
        {
            string sql = "";
            string col_import = string.Empty;
            //dt_import = DateTime.MinValue;
            string colnames = string.Empty;
            int RowCnt = 1;

            foreach (DataRow dr in dt_1.Rows)
            {
                colnames += "[" + dr[0].ToString() + "],";

                string ColName = dr[0].ToString();
                string ColValue = dr[1].ToString();
                if (ColName.Equals("RECORD_DATETIME", StringComparison.OrdinalIgnoreCase))
                    continue;
                if (ColName.Equals("RECORD_SEQ", StringComparison.OrdinalIgnoreCase))
                    continue;
                if (ColName.Equals("GROUP_DATETIME", StringComparison.OrdinalIgnoreCase))
                    continue;
                if (!IsNull(ColName, dt_0))
                {
                    if (ColValue.Equals(string.Empty))
                    {
                        throw new Exception(dr[0].ToString() + "的值不能为空！");
                    }
                }
            }

            foreach (DataColumn dc in dt_2.Columns)
            {
                colnames += "[" + dc.ColumnName + "],";
            }
            colnames = colnames.Trim(',');
            col_import = colnames;

            foreach (DataRow dr in dt_2.Rows)
            {
                string sql_dv = "";
                string fixColVal = string.Format("'{0}',{1},'{2}','{3}','{4}','{5}'" +
                    ",'{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}',",
                    new object[]{dt_import.ToString("yyyy-MM-dd HH:mm:ss"),RowCnt++,dt_1.Rows[2][1].ToString(),dt_1.Rows[3][1].ToString(),dt_1.Rows[4][1].ToString(),dt_1.Rows[5][1].ToString(),
                        dt_1.Rows[6][1].ToString(),dt_1.Rows[7][1].ToString(),dt_1.Rows[8][1].ToString(),dt_import.ToString("yyyy-MM-dd HH:mm:ss"),dt_1.Rows[10][1].ToString(),dt_1.Rows[11][1].ToString(),
                        dt_1.Rows[12][1].ToString(),dt_1.Rows[13][1].ToString(),dt_1.Rows[14][1].ToString(),dt_1.Rows[15][1].ToString(),dt_1.Rows[16][1].ToString(),dt_1.Rows[17][1].ToString()});

                foreach (DataColumn dc in dt_2.Columns)
                {
                    string str_val = dr[dc.ColumnName].ToString();
                    if (str_val.Equals(string.Empty))
                        sql_dv += "null,";
                    else if (dc.ColumnName == "HOOKUP_2D_BARCODE")
                        sql_dv += "'" + dr[dc.ColumnName].ToString() + "',";
                    else if (dc.ColumnName == "ACA_DATE_CODE")
                        sql_dv += "'" + dr[dc.ColumnName].ToString() + "',";
                    else
                        sql_dv += dr[dc.ColumnName].ToString() + ",";
                }

                sql_dv = sql_dv.Substring(0, sql_dv.Length - 1);
                sql = string.Format("insert into rt_QPM_Hookup_New({0}) values ({1})", colnames, fixColVal + sql_dv);
                //QPMDAL._Trace_ExecuteNonQuery(sql);
            }
        }

        private void InsertSpecData(DataTable dt_1, DataTable dt_2, DataTable dt_0, DateTime dt_import)
        {
            string sql = "";
            string colnames = string.Empty;

            foreach( DataRow dr in dt_1.Rows )
            {
                colnames += colnames += "[" + dr[0].ToString() + "],";

                string ColName = dr[0].ToString();
                string ColValue = dr[1].ToString();

                if (!IsNull(ColName, dt_0))
                {
                    if (ColValue.Equals(string.Empty))
                    {
                        throw new Exception(dr[0].ToString() + "的值不能为空！");
                    }
                }
            }

            foreach (DataColumn dc in dt_2.Columns)
            {
                colnames += "[" + dc.ColumnName + "],";
            }
            colnames = colnames.Trim(',');

            foreach (DataRow dr in dt_2.Rows)
            {
                string sql_dv = "";
                string fixColVal = string.Format(" '{0}','{1}','{2}','{3}','{4}', ", new object[] { dt_1.Rows[0][1].ToString(), dt_1.Rows[1][1].ToString(), dt_1.Rows[2][1].ToString(), dt_1.Rows[3][1].ToString(), dt_import.ToString("yyyy-MM-dd HH:mm:ss") });

                foreach (DataColumn dc in dt_2.Columns)
                {
                    string str_val = dr[dc.ColumnName].ToString();
                    if (str_val.Equals(string.Empty))
                        sql_dv += "null,";
                    else if (dc.ColumnName == "COMMODITY_METRIC")
                        sql_dv += "'" + dr[dc.ColumnName].ToString() + "',";
                    else if (dc.ColumnName == "SPEC_TYPE_CODE")
                        sql_dv += "'" + dr[dc.ColumnName].ToString() + "',";
                    else if (dc.ColumnName == "UNITS_OF_MEASURE")
                        sql_dv += "'" + dr[dc.ColumnName].ToString() + "',";
                    else if (dc.ColumnName == "CTQ")
                        sql_dv += "'" + dr[dc.ColumnName].ToString() + "',";
                    else if (dc.ColumnName == "SAMPLE_RATE_GROUPING")
                        sql_dv += "'" + dr[dc.ColumnName].ToString() + "',";
                    else
                        sql_dv += dr[dc.ColumnName].ToString() + ",";
                }
                sql_dv = sql_dv.Substring(0, sql_dv.Length - 1);
                sql = string.Format("insert into rt_QPMSpec_Hookup({0}) values ({1})", colnames, fixColVal + sql_dv);
                //QPMDAL._Trace_ExecuteNonQuery(sql);
            }
        }

        public static DataTable GetData(string sql)
        {
            DataTable dt = new DataTable();
            return dt;//QPMDAL.GetData(sql);
        }

        public static bool IsNull(string ColName, DataTable dt_0)
        {
            return dt_0.Columns[ColName].AllowDBNull;
        }

        public static DataTable ReadQPMFile(string csvFile)
        {
            DataTable dt = new DataTable();
            string[] strs;
            FileInfo fi = new FileInfo(csvFile);
            try
            {
                using (StreamReader sr = new StreamReader(fi.OpenRead(), Encoding.GetEncoding("GB2312"), true))
                {
                    string str = sr.ReadToEnd();
                    strs = str.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
                }
                string[] strs_col = strs[5].Split(',');   //第六行为列名            
                for (int i = 0; i < strs_col.Length; i++)
                {
                    dt.Columns.Add(strs_col[i].ToString().Trim('"'));
                }
                for (int i = 6; i < strs.Length; i++)
                {
                    string[] val = strs[i].Replace('"', '\r').Split(',');
                    dt.Rows.Add(val);
                }
                dt.Rows[dt.Rows.Count - 1].Delete();
            }
            catch
            {
                throw;
            }
            return dt;
        }

        public static DataTable ReadQDMFile(string csvFile)
        {
            DataTable dt = new DataTable();
            string[] strs;
            FileInfo fi = new FileInfo(csvFile);
            try
            {
                using (StreamReader sr = new StreamReader(fi.OpenRead(), Encoding.GetEncoding("GB2312"), true))
                {
                    string str = sr.ReadToEnd();
                    strs = str.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
                }
                int x = strs[0].Length;
                string[] strs_col = strs[0].Split(',');                //取第一行作为列名。只需要26列之后的列和数据，否则点击插入按钮时会出错
                for (int i = 0; i < strs_col.Length; i++)
                {
                    dt.Columns.Add(strs_col[i].ToString().Trim('"'));
                }
                for (int i = 1; i < strs.Length; i++)             //放置数据到 dataGridView2
                {
                    string[] val = strs[i].Split(',');
                    dt.Rows.Add(val);
                }
            }
            catch
            {
                throw;
            }
            return dt;
        }

        public static List<string> GetCol()
        {
            List<string> _listCol = new List<string>();
            _listCol.Add("MFG_SITE_DUNS");
            _listCol.Add("SUB_SUPPLIER_NAME");
            _listCol.Add("DATA_SOURCE_TYPE");
            _listCol.Add("PART_NUM");
            _listCol.Add("PART_REV");
            _listCol.Add("DRAWING_NUM");
            _listCol.Add("DRAWING_REV");
            _listCol.Add("GROUP_DATETIME");
            _listCol.Add("SBR_NUM");
            _listCol.Add("LOT_NUM");
            _listCol.Add("RECORD_DATETIME");
            _listCol.Add("RECORD_SEQ");
            _listCol.Add("REFERENCE_PART_NUM");
            _listCol.Add("REFERENCE_PART_REV");
            _listCol.Add("IONIC_SURFACE_AREA");
            _listCol.Add("IONIC_TEST_PIECES");
            _listCol.Add("IONIC_ANIONS_TOTAL_ANION");
            _listCol.Add("IONIC_CATIONS_AMMONIUM");
            _listCol.Add("DHS_SURFACE_AREA");
            _listCol.Add("DHS_TEST_PIECES");
            _listCol.Add("DHS_ACETOPHENONE_DERIVATIVES");
            _listCol.Add("DHS_ALCOHOLS");
            _listCol.Add("DHS_ALIPHATIC_AROMATIC_ESTERS");
            _listCol.Add("DHS_ALKYLACRYLATE");
            _listCol.Add("DHS_AMINE_AMIDES");
            _listCol.Add("DHS_BENZOIC_ACID_DERIVATIVES");

            _listCol.Add("DHS_BUTYLATED_HYDROXYL_TOLUENE");
            _listCol.Add("DHS_ETHYL_HEXANOL");
            _listCol.Add("DHS_HALOGEN_HYDROCARBONS");
            _listCol.Add("DHS_ALIPHATIC_HYDROCARBONS");
            _listCol.Add("DHS_AROMATIC_HYDROCARBONS");
            _listCol.Add("DHS_ORGANIC_ACID");
            _listCol.Add("DHS_ORGANIC_SULFUR");
            _listCol.Add("DHS_OTHERS");
            _listCol.Add("DHS_PHENOLS_DERIVATIVES");
            _listCol.Add("DHS_SILOXANES");
            _listCol.Add("DHS_STYRENE");
            _listCol.Add("DHS_TOTAL_OUTGASSING");
            _listCol.Add("FTIR_SURFACE_AREA");
            _listCol.Add("FTIR_TEST_PIECES");
            _listCol.Add("FTIR_SILICONE");
            _listCol.Add("PARTICLE_LPC_SURFACE_AREA");
            _listCol.Add("PARTICLE_LPC_TEST_PIECES");
            _listCol.Add("PARTICLE_LPC_03");
            _listCol.Add("PARTICLE_HPA_SURFACE_AREA");
            _listCol.Add("PARTICLE_HPA_TEST_PIECES");
            _listCol.Add("HPA_CLASS_5_1_MGSIO");
            _listCol.Add("HPA_CLASS_1_TOTAL_HPA");
            _listCol.Add("HPA_CLASS_3_1_SST_FEBASED_FEO");
            _listCol.Add("HPA_CLASS_3_2_SN_BASED");
            _listCol.Add("HPA_CLASS_3_TOTAL_SEMI_HPA");
            _listCol.Add("HPA_CLASS_4_TOTAL_SOFT_METAL");

            _listCol.Add("HPA_CLASS_5_TOTAL_ENV");
            _listCol.Add("SPARE_01");
            _listCol.Add("SPARE_02");
            _listCol.Add("SPARE_03");
            _listCol.Add("SPARE_04");
            _listCol.Add("SPARE_05");
            _listCol.Add("SPARE_06");
            _listCol.Add("SPARE_07");
            _listCol.Add("SPARE_08");
            _listCol.Add("SPARE_09");
            _listCol.Add("SPARE_10");

            return _listCol;

        }
    }
}
